﻿Imports MySql.Data.MySqlClient

Public Class Databases
    Function mysql_non_query(ByVal command_text As String, Optional ByVal is_local As Boolean = True, Optional ByVal host As String = "",
                               Optional ByVal port As Integer = 3306, Optional ByVal username As String = "", Optional ByVal password As String = "",
                               Optional ByVal database As String = "")
        Dim connection As MySqlConnection
        Dim command As MySqlCommand

        If is_local = True Then
            host = dbHost
            port = dbPort
            username = dbUser
            password = dbPass
            database = dbName
        End If

        Dim connection_string As String = String.Format("Data Source={0};User Id={1};Password={2};Database={3};Convert Zero Datetime=True", host, username, password, database)

        connection = New MySqlConnection(connection_string)
        Try
            connection.Open()

            command = connection.CreateCommand()
            command.CommandText = command_text
            command.ExecuteNonQuery()
            command.Dispose()

            connection.Close()
            connection.Dispose()
        Catch ex As Exception
            Return False
        End Try

        Return True
    End Function

    Function mysql_query(ByVal command_text As String, Optional ByVal col_index As Integer = -1, Optional ByVal is_local As Boolean = True, Optional ByVal host As String = "",
                               Optional ByVal port As Integer = 3306, Optional ByVal username As String = "", Optional ByVal password As String = "",
                               Optional ByVal database As String = "")
        If is_local = True Then
            host = dbHost
            port = dbPort
            username = dbUser
            password = dbPass
            database = dbName
        End If

        Dim connection_string As String = String.Format("Data Source={0};User Id={1};Password={2};Database={3};Convert Zero Datetime=True", host, username, password, database)
        Dim connection As New MySqlConnection(connection_string)
        connection.Open()

        If col_index < 0 Then 'return data table
            Dim data As New DataTable
            Dim adapter As New MySqlDataAdapter(command_text, connection)
            adapter.Fill(data)
            adapter.Dispose()
            data.Dispose()
            connection.Close()
            connection.Dispose()

            Return data
        Else 'return reader
            Dim command As MySqlCommand = connection.CreateCommand()
            Dim reader As MySqlDataReader
            Dim result As String
            command.CommandText = command_text
            reader = command.ExecuteReader()
            reader.Read()
            result = reader.GetValue(col_index).ToString
            command.Dispose()
            connection.Close()
            connection.Dispose()

            Return result
        End If

        Return 0
    End Function

    Function show_databases(Optional ByVal is_local As Boolean = True, Optional ByVal host As String = "",
                             Optional ByVal port As Integer = 3306, Optional ByVal username As String = "", Optional ByVal password As String = "")
        If is_local = True Then
            host = dbHost
            port = dbPort
            username = dbUser
            password = dbPass
        End If

        Dim connection_string As String = String.Format("Data Source={0};User Id={1};Password={2};Convert Zero Datetime=True", host, username, password)

        Dim connection As New MySqlConnection(connection_string)
        Dim data As New DataTable
        connection.Open()

        Dim adapter As New MySqlDataAdapter("SHOW DATABASES", connection)
        adapter.Fill(data)
        adapter.Dispose()
        data.Dispose()

        connection.Close()
        connection.Dispose()

        Return data
    End Function

    Function mysql_isconnected(Optional ByVal is_local As Boolean = False, Optional ByVal host As String = "", Optional ByVal port As Integer = 3306,
                              Optional ByVal username As String = "", Optional ByVal password As String = "",
                              Optional ByVal database As String = "")
        If is_local = True Then
            host = dbHost
            port = dbPort
            username = dbUser
            password = dbPass
            database = dbName
        End If

        Dim connection_string As String = String.Format("Data Source={0};User Id={1};Password={2};Database={3};Convert Zero Datetime=True", host, username, password, database)

        Dim connection As New MySqlConnection(connection_string)
        Try
            connection.Open()
            connection.Close()
            connection.Dispose()
        Catch ex As Exception
            Return False
            Exit Function
        End Try

        Return True
    End Function
End Class
